using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Windows.Forms.DataVisualization.Charting;
using System.IO;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMPostLogDAL
    /// 完成日期：2011年7月29日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSQLITEPostLog
    {
        /// <summary>
        /// 插入发送记录
        /// </summary>
        /// <param name="strEmail">电子邮件</param>
        /// <param name="strType">日志类型</param>
        /// <param name="strContent">错误信息</param>
        public static void InsertPostLog(string strEmail,string strType,string strContent)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into PostLog(log_id,log_email,log_type,log_content,add_date) " +
                    " values(" + GetAutoAddNum() + ",'" + strEmail + "','" + strType + "','" + strContent + "',datetime('now','localtime'))";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入发送记录 -> " + ex.Message);
            }
        }

        public static void InsertPostLog2(string strEmail, string strType, string strContent,string strAddDate)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into PostLog(log_id,log_email,log_type,log_content,add_date) " +
                    " values(" + GetAutoAddNum() + ",'" + strEmail + "','" + strType + "','" + strContent + "','" + Convert.ToDateTime(strAddDate).ToString("s") + "')";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入发送记录 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 清空数据
        /// </summary>
        public static void DeletePostLog()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete  from PostLog";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("清空数据 -> " + ex.Message);
            }
        }

        /// <summary>
        /// PostLog获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select log_id from PostLog order by log_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["log_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("PostLog获取最大ID -> " + ex.Message);
            }

            return iNum;
        }


        /// <summary>
        /// 绘制发送数量图表
        /// </summary>
        /// <param name="chart">chart控件名称</param>
        /// <param name="strAxisYTitle">Y轴标题</param>
        /// <param name="bShowAsLabel">是否显示标记点的值</param>
        /// <param name="bShowMarker">是否显示标记点</param>
        public static void ChartForPostLog(System.Windows.Forms.DataVisualization.Charting.Chart chart, string strAxisYTitle, bool bShowAsLabel, bool bShowMarker,string strWhere)
        {
            try
            {
                chart.Series["Series1"].Points.Clear();
                chart.Series["Series2"].Points.Clear();
                chart.Series["Series3"].Points.Clear();
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT strftime('%Y-%m-%d', add_date) AS gdate, COUNT(log_id) AS cnum FROM PostLog " + strWhere + "GROUP BY strftime('%Y-%m-%d', add_date)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strDate = dr["gdate"].ToString();
                    string strNum = dr["cnum"].ToString();
                    int iNum1 = GetPostCountNum(strDate, "email_success");
                    int iNum2 = GetPostCountNum(strDate, "email_fail");
                    chart.Series["Series1"].Points.AddXY(strDate, strNum);
                    chart.Series["Series2"].Points.AddXY(strDate, iNum1);
                    chart.Series["Series3"].Points.AddXY(strDate, iNum2);
                }
                dr.Dispose();
                cn.Close();

                chart.Series["Series1"].IsVisibleInLegend = true;
                chart.Series["Series1"].LegendText = "发送总量";
                chart.Series["Series1"].IsValueShownAsLabel = bShowAsLabel;//显示数据点上的值
                if (bShowMarker)
                {
                    chart.Series["Series1"].MarkerStyle = System.Windows.Forms.DataVisualization.Charting.MarkerStyle.Circle;//标记数据点的类型
                    chart.Series["Series1"].MarkerSize = 10;//标记数据点
                    chart.Series["Series1"].MarkerColor = Color.FromArgb(150, 203, 40);//标记点的颜色Color.FromArgb(255, 70, 31);Color.FromArgb(48, 48, 208);
                    chart.Series["Series1"].MarkerBorderColor = Color.FromArgb(255, 251, 222);
                    chart.Series["Series1"].MarkerBorderWidth = 1;
                }
                chart.Series["Series1"].Color = Color.FromArgb(150, 203, 40);//绘图主线的颜色
                chart.Series["Series1"].BorderWidth = 3;
                chart.Series["Series1"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.FastLine;//绘图类型

                chart.Series["Series2"].IsVisibleInLegend = true;
                chart.Series["Series2"].LegendText = "发送成功";
                chart.Series["Series2"].IsValueShownAsLabel = bShowAsLabel;//显示数据点上的值
                if (bShowMarker)
                {
                    chart.Series["Series2"].MarkerStyle = System.Windows.Forms.DataVisualization.Charting.MarkerStyle.Circle;//标记数据点的类型
                    chart.Series["Series2"].MarkerSize = 10;//标记数据点
                    chart.Series["Series2"].MarkerColor = Color.FromArgb(255, 70, 31);//标记点的颜色Color.FromArgb(255, 70, 31);Color.FromArgb(48, 48, 208);
                    chart.Series["Series2"].MarkerBorderColor = Color.FromArgb(255, 251, 222);
                    chart.Series["Series2"].MarkerBorderWidth = 1;
                }
                chart.Series["Series2"].Color = Color.FromArgb(255, 70, 31);//绘图主线的颜色
                chart.Series["Series2"].BorderWidth = 3;
                chart.Series["Series2"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.FastLine;//绘图类型

                chart.Series["Series3"].IsVisibleInLegend = true;
                chart.Series["Series3"].LegendText = "发送失败";
                chart.Series["Series3"].IsValueShownAsLabel = bShowAsLabel;//显示数据点上的值
                if (bShowMarker)
                {
                    chart.Series["Series3"].MarkerStyle = System.Windows.Forms.DataVisualization.Charting.MarkerStyle.Circle;//标记数据点的类型
                    chart.Series["Series3"].MarkerSize = 10;//标记数据点
                    chart.Series["Series3"].MarkerColor = Color.FromArgb(22, 133, 169);//标记点的颜色Color.FromArgb(255, 70, 31);Color.FromArgb(48, 48, 208);
                    chart.Series["Series3"].MarkerBorderColor = Color.FromArgb(255, 251, 222);
                    chart.Series["Series3"].MarkerBorderWidth = 1;
                }
                chart.Series["Series3"].Color = Color.FromArgb(22, 133, 169);//绘图主线的颜色
                chart.Series["Series3"].BorderWidth = 3;
                chart.Series["Series3"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.FastLine;//绘图类型

                chart.ChartAreas[0].AxisX.MajorGrid.LineColor = Color.FromArgb(247, 227, 173);
                //chart.ChartAreas[0].AxisX.MajorGrid.Interval = 5;//网格间隔
                //chart.ChartAreas[0].AxisX.MinorGrid.Interval = 5;
                chart.ChartAreas[0].AxisY.MajorGrid.LineColor = Color.FromArgb(247, 227, 173);
                //chart.ChartAreas[0].AxisY.MajorGrid.Interval = 5;
                //chart.ChartAreas[0].AxisY.MinorGrid.Interval = 5;

                chart.ChartAreas[0].BackColor = Color.FromArgb(255, 251, 222); //绘图区背景色
                chart.ChartAreas[0].AxisX.LineColor = Color.FromArgb(123, 65, 16); //X轴颜色
                chart.ChartAreas[0].AxisX.LineWidth = 2; //X轴宽度
                chart.ChartAreas[0].AxisX.Title = "邮件发送状况走势图(" + DateTime.Now.ToString("yyyy年M月d日") + ") http://www.cloudin.so"; //X轴标题
                chart.ChartAreas[0].AxisX.MajorTickMark.LineColor = Color.FromArgb(123, 65, 16);//X轴刻度颜色
                chart.ChartAreas[0].AxisX.MajorTickMark.LineWidth = 2;//X轴刻度宽度

                chart.ChartAreas[0].AxisY.LineColor = Color.FromArgb(123, 65, 16); //Y轴颜色
                chart.ChartAreas[0].AxisY.LineWidth = 2; //Y轴宽度
                chart.ChartAreas[0].AxisY.Title = strAxisYTitle;//Y轴标题
                chart.ChartAreas[0].AxisY.MajorTickMark.LineColor = Color.FromArgb(123, 65, 16);//Y轴刻度颜色
                chart.ChartAreas[0].AxisY.MajorTickMark.LineWidth = 2;//Y轴刻度宽度
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绘制文章数量图表 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 根据类别获取统计数据
        /// </summary>
        /// <param name="strDate"></param>
        /// <param name="strPostStatus"></param>
        /// <returns></returns>
        public static int GetPostCountNum(string strDate, string strPostStatus)
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(log_id) AS cnum FROM PostLog where (log_type='" + strPostStatus + "') and (strftime('%Y-%m-%d', add_date) = '" + strDate + "')";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["cnum"].ToString());
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("根据类别获取统计数据 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 导出发送历史记录到txt
        /// </summary>
        /// <returns>返回结果</returns>
        public static string ExportPostLogForTxt()
        {
            string strReturnValue = "";
            try
            {
                int k = 1;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT strftime('%Y-%m-%d', add_date) AS gdate, COUNT(log_id) AS cnum FROM PostLog GROUP BY strftime('%Y-%m-%d', add_date)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strDate = dr["gdate"].ToString();
                    string strNum = dr["cnum"].ToString();
                    int iNum1 = GetPostCountNum(strDate, "email_success");
                    int iNum2 = GetPostCountNum(strDate, "email_fail");
                    string strValue = k + "	" + strDate + "	" + strNum + "	" + iNum1 + "	" + iNum2 + "\r\n";
                    strReturnValue += strValue;
                    k++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出发送历史记录到 -> " + ex.Message);
            }

            return "0	发送日期	发送总数	成功	失败\r\n" + strReturnValue;

        }

        /// <summary>
        /// 导出发送历史记录到Excel
        /// </summary>
        /// <param name="strPath">保存路径</param>
        /// <param name="strFileName">文件名</param>
        public static void ExportPostLogForExcel(string strPath, string strFileName)
        {
            try
            {
                int k = 1;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMLogConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT strftime('%Y-%m-%d', add_date) AS gdate, COUNT(log_id) AS cnum FROM PostLog GROUP BY strftime('%Y-%m-%d', add_date)";
                SQLiteDataAdapter da = new SQLiteDataAdapter(strSql, cn);
                DataSet ds = new DataSet();
                da.Fill(ds, "History");
                DataTable dt = ds.Tables["History"];

                string strPathName = strPath + strFileName;
                FileStream fs = new FileStream(strPathName, FileMode.Create, FileAccess.Write);
                StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
                sw.WriteLine("序号,日期,发送总数,成功,失败");
                foreach (DataRow dr in dt.Rows)
                {
                    string strDate = dr["gdate"].ToString();
                    int iNum1 = GetPostCountNum(strDate, "email_success");
                    int iNum2 = GetPostCountNum(strDate, "email_fail");
                    sw.WriteLine(k + "," + dr["gdate"] + "," + dr["cnum"] + "," + iNum1 + "," + iNum2);
                    k++;
                }
                sw.Close();
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(strPathName));
                System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被
                System.Web.HttpContext.Current.Response.WriteFile(strPathName); // 把文件流发送到客户端
                System.Web.HttpContext.Current.Response.End();

                da.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出发送历史记录到Excel -> " + ex.Message);
            }
        }
        
    }
}
